// 批量生成SQL插入语句的脚本 - 确保数据关联正确
const fs = require('fs');
const path = require('path');

// 真实中文内容数据
const departmentNames = ['技术部', '市场部', '财务部', '人力资源部', '行政部', '销售部', '客服部', '产品部'];
const costItemNames = ['工资薪金', '办公设备', '差旅费', '会议费', '培训费', '水电费', '通讯费', '材料费', '广告费', '招待费'];
const costItemTypes = [
  { type: 1, name: '人力成本' },
  { type: 2, name: '物料成本' },
  { type: 3, name: '设备成本' },
  { type: 4, name: '行政费用' },
  { type: 5, name: '差旅费用' },
  { type: 6, name: '营销费用' },
  { type: 7, name: '研发费用' },
  { type: 8, name: '其他费用' }
];
const realNames = ['张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十', '郑十一', '王十二'];
const descriptions = [
  '日常办公支出',
  '项目执行费用',
  '员工培训费用',
  '部门活动经费',
  '设备采购费用',
  '系统维护费用',
  '市场推广费用',
  '客户招待费用'
];

// 存储生成的数据，用于保证外键关联
const generatedData = {
  departments: [],
  costItems: [],
  users: []
};

// 生成随机日期
function generateRandomDate() {
  const date = new Date();
  date.setDate(date.getDate() - Math.floor(Math.random() * 365));
  return `'${date.toISOString().split('T')[0]}'`;
}

// 生成随机时间
function generateRandomDateTime() {
  const date = new Date();
  date.setDate(date.getDate() - Math.floor(Math.random() * 365));
  return `'${date.toISOString().replace('T', ' ').split('.')[0]}'`;
}

// 生成随机邮箱
function generateRandomEmail(username) {
  return `'${username}@company.com'`;
}

// 生成随机手机号
function generateRandomPhone() {
  return `'13${Math.floor(Math.random() * 1000000000).toString().padStart(9, '0')}'`;
}

// 生成用户表数据
function generateUserTableData() {
  console.log('生成用户表数据...');
  let sql = '-- 用户表插入语句\n';
  
  // 第一条记录固定为admin
  const adminSql = `INSERT INTO user (username, password, real_name, email, phone, department_id, role_type, status, last_login_time) 
VALUES ('admin', '123456', '系统管理员', 'admin@company.com', '13800138000', 1, 4, 1, ${generateRandomDateTime()});\n`;
  
  sql += adminSql;
  generatedData.users.push({ id: 1, username: 'admin' });
  
  // 生成其他用户，共10条
  for (let i = 2; i <= 10; i++) {
    const username = `user${i}`;
    const realName = realNames[(i - 2) % realNames.length];
    const departmentId = Math.floor(Math.random() * departmentNames.length) + 1;
    const roleType = Math.floor(Math.random() * 3) + 1; // 1-普通员工 2-部门经理 3-财务人员
    
    sql += `INSERT INTO user (username, password, real_name, email, phone, department_id, role_type, status, last_login_time) 
VALUES ('${username}', '123456', '${realName}', ${generateRandomEmail(username)}, ${generateRandomPhone()}, ${departmentId}, ${roleType}, 1, ${generateRandomDateTime()});\n`;
    
    generatedData.users.push({ id: i, username });
  }
  
  return sql;
}

// 生成部门表数据
function generateDepartmentTableData() {
  console.log('生成部门表数据...');
  let sql = '-- 部门表插入语句\n';
  
  departmentNames.forEach((name, index) => {
    const id = index + 1;
    const deptCode = `DEPT${String(id).padStart(3, '0')}`;
    const parentId = index === 0 ? 0 : Math.floor(Math.random() * index) + 1; // 随机上级部门
    const managerId = Math.floor(Math.random() * generatedData.users.length) + 1;
    const budgetAmount = (Math.random() * 1000000).toFixed(2);
    
    sql += `INSERT INTO department (dept_name, dept_code, parent_id, manager_id, cost_center, budget_amount, status, sort_order) 
VALUES ('${name}', '${deptCode}', ${parentId}, ${managerId}, 'CC${String(id).padStart(3, '0')}', ${budgetAmount}, 1, ${id});\n`;
    
    generatedData.departments.push({ id, name, code: deptCode });
  });
  
  return sql;
}

// 生成成本项目表数据
function generateCostItemTableData() {
  console.log('生成成本项目表数据...');
  let sql = '-- 成本项目表插入语句\n';
  
  costItemNames.forEach((name, index) => {
    const id = index + 1;
    const itemCode = `ITEM${String(id).padStart(3, '0')}`;
    const itemType = costItemTypes[Math.floor(Math.random() * costItemTypes.length)].type;
    const parentId = index === 0 ? 0 : Math.floor(Math.random() * index) + 1;
    const budgetCycle = Math.floor(Math.random() * 3) + 1;
    const description = descriptions[index % descriptions.length];
    
    sql += `INSERT INTO cost_item (item_code, item_name, item_type, parent_id, budget_cycle, is_leaf, status, description, sort_order) 
VALUES ('${itemCode}', '${name}', ${itemType}, ${parentId}, ${budgetCycle}, 1, 1, '${description}', ${id});\n`;
    
    generatedData.costItems.push({ id, name, code: itemCode });
  });
  
  return sql;
}

// 生成成本记录表数据
function generateCostRecordTableData() {
  console.log('生成成本记录表数据...');
  let sql = '-- 成本记录表插入语句\n';
  
  // 生成50条成本记录
  for (let i = 1; i <= 50; i++) {
    const recordNo = `CR${new Date().getFullYear()}${String(i).padStart(5, '0')}`;
    const departmentId = Math.floor(Math.random() * generatedData.departments.length) + 1;
    const costItemId = Math.floor(Math.random() * generatedData.costItems.length) + 1;
    const applicantId = Math.floor(Math.random() * generatedData.users.length) + 1;
    const amount = (Math.random() * 50000).toFixed(2);
    const occurrenceDate = generateRandomDate();
    const recordDate = occurrenceDate;
    const costType = Math.floor(Math.random() * 3) + 1;
    const paymentStatus = Math.floor(Math.random() * 3) + 1;
    const description = descriptions[i % descriptions.length];
    const approverId = Math.floor(Math.random() * generatedData.users.length) + 1;
    const approvalStatus = Math.floor(Math.random() * 3) + 1;
    const approvalComment = approvalStatus === 2 ? '审批通过' : approvalStatus === 3 ? '需补充材料' : '';
    const approvalTime = approvalStatus !== 1 ? generateRandomDateTime() : 'NULL';
    
    sql += `INSERT INTO cost_record (record_no, department_id, cost_item_id, applicant_id, amount, occurrence_date, record_date, cost_type, payment_status, description, attachment, approver_id, approval_status, approval_comment, approval_time) 
VALUES ('${recordNo}', ${departmentId}, ${costItemId}, ${applicantId}, ${amount}, ${occurrenceDate}, ${recordDate}, ${costType}, ${paymentStatus}, '${description}', NULL, ${approverId}, ${approvalStatus}, ${approvalComment ? `'${approvalComment}'` : 'NULL'}, ${approvalTime});\n`;
  }
  
  return sql;
}

// 生成预算配置表数据
function generateBudgetConfigTableData() {
  console.log('生成预算配置表数据...');
  let sql = '-- 预算配置表插入语句\n';
  
  const currentYear = new Date().getFullYear();
  
  // 为每个部门生成一些预算配置
  generatedData.departments.forEach(dept => {
    // 为每个部门选择3个成本项目设置预算
    for (let i = 0; i < 3; i++) {
      const costItemIndex = Math.floor(Math.random() * generatedData.costItems.length);
      const costItemId = generatedData.costItems[costItemIndex].id;
      const budgetYear = currentYear;
      const budgetCycle = Math.floor(Math.random() * 3) + 1;
      const cycleNumber = budgetCycle === 1 ? Math.floor(Math.random() * 12) + 1 : 
                         budgetCycle === 2 ? Math.floor(Math.random() * 4) + 1 : 1;
      const budgetAmount = (Math.random() * 500000).toFixed(2);
      const usedAmount = (Math.random() * parseFloat(budgetAmount)).toFixed(2);
      const remainingAmount = (parseFloat(budgetAmount) - parseFloat(usedAmount)).toFixed(2);
      const alertThreshold = 80.00;
      const isAlert = parseFloat(usedAmount) / parseFloat(budgetAmount) > 0.8 ? 1 : 0;
      const creatorId = Math.floor(Math.random() * generatedData.users.length) + 1;
      
      sql += `INSERT INTO budget_config (department_id, cost_item_id, budget_year, budget_cycle, cycle_number, budget_amount, used_amount, remaining_amount, alert_threshold, is_alert, status, creator_id) 
VALUES (${dept.id}, ${costItemId}, ${budgetYear}, ${budgetCycle}, ${cycleNumber}, ${budgetAmount}, ${usedAmount}, ${remainingAmount}, ${alertThreshold}, ${isAlert}, 1, ${creatorId});\n`;
    }
  });
  
  return sql;
}

// 主函数
function main() {
  console.log('开始生成批量插入SQL语句...');
  
  let allSQL = '-- 批量插入SQL语句 - 确保数据关联正确\n\n';
  
  // 按顺序生成表数据，确保依赖关系正确
  // 1. 先生成用户表（因为部门表需要用户ID作为部门经理）
  allSQL += generateUserTableData() + '\n';
  
  // 2. 生成部门表
  allSQL += generateDepartmentTableData() + '\n';
  
  // 3. 生成成本项目表
  allSQL += generateCostItemTableData() + '\n';
  
  // 4. 生成成本记录表（依赖用户、部门、成本项目）
  allSQL += generateCostRecordTableData() + '\n';
  
  // 5. 生成预算配置表（依赖部门、成本项目、用户）
  allSQL += generateBudgetConfigTableData() + '\n';
  
  // 写入SQL文件，使用utf8编码确保中文正常显示
  const sqlFilePath = path.join(__dirname, 'batch_insert_data.sql');
  fs.writeFileSync(sqlFilePath, allSQL, 'utf8');
  console.log(`SQL文件已生成: ${sqlFilePath}`);
  console.log('SQL文件生成完成!');
  console.log(`\n数据统计：`);
  console.log(`- 用户表: ${generatedData.users.length} 条记录（包含admin账户）`);
  console.log(`- 部门表: ${generatedData.departments.length} 条记录`);
  console.log(`- 成本项目表: ${generatedData.costItems.length} 条记录`);
  console.log(`- 成本记录表: 50 条记录`);
  console.log(`- 预算配置表: ${generatedData.departments.length * 3} 条记录`);
}

// 执行主函数
main();